import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;

import static java.lang.System.exit;


public class Student {
    String id;//学号
    String name;//姓名
    String place;//籍贯
    static AnnotationConfigApplicationContext ctx=new AnnotationConfigApplicationContext(Config.class);
    static DataSource dataSource = ctx.getBean("dateSourse",DataSource.class);

    //全参构造方法
    Student(String id, String name, String place) {
        this.id = id;
        this.name = name;
        this.place = place;
    }


    //增加学生
    void creatStudent() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生学号：");
        String I = sc.next();
        System.out.print("\n请输入学生姓名：");
        String N = sc.next();
        System.out.print("\n请输入学生籍贯：");
        String P = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String insertsql = "insert into student values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(insertsql);
        ps.setString(1, I);
        ps.setString(2, N);
        ps.setString(3, P);
        ps.executeUpdate();
        conn.close();
        System.out.println("增加成功");
    }

    //按学号删除
    void deleteById() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生学号：");
        String I = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String deletesql = "delete from student where id=?";
        PreparedStatement ps = conn.prepareStatement(deletesql);
        ps.setString(1, I);
        ps.executeUpdate();
        conn.close();
        System.out.println("删除成功");
    }

    //按姓名删除
    void deleteByName() throws SQLException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生姓名：");
        String N = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String deletesql = "delete from student where name=?";
        PreparedStatement ps = conn.prepareStatement(deletesql);
        ps.setString(1, N);
        ps.executeUpdate();
        conn.close();
        System.out.println("删除成功");
    }

    //修改籍贯
    void setPlace() throws SQLException, ClassNotFoundException {
        selectById();
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请再次输入修改籍贯的学号：");
        String i = sc.next();
        System.out.print("\n请输入修改后的籍贯：");
        String P = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String updatesql = "update student set place=? where id=?";
        PreparedStatement ps = conn.prepareStatement(updatesql);
        ps.setString(1, P);
        ps.setString(2, i);
        ps.executeUpdate();
        conn.close();
        System.out.println("修改成功");
    }

    //查询所有
    void selectAll() throws ClassNotFoundException, SQLException {
        Connection conn = null;
        conn = dataSource.getConnection();
        String selectsql = "select * from student";
        PreparedStatement ps = conn.prepareStatement(selectsql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println("学号：" + rs.getString("id") + "   姓名：" + rs.getString("name") + "   籍贯：" + rs.getString("place"));
        }
        conn.close();
    }

    //按学号查询
    void selectById() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生学号：");
        String I = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String selectsql = "select * from student where id=?";
        PreparedStatement ps = conn.prepareStatement(selectsql);
        ps.setString(1, I);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println("学号：" + rs.getString("id") + "   姓名：" + rs.getString("name") + "   籍贯：" + rs.getString("place"));
        }
        conn.close();
    }

    //按姓名查询
    void selectByName() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生姓名：");
        String N = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        String selectsql = "select * from student where name=?";
        PreparedStatement ps = conn.prepareStatement(selectsql);
        ps.setString(1, N);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println("学号：" + rs.getString("id") + "   姓名：" + rs.getString("name") + "   籍贯：" + rs.getString("place"));
        }
        conn.close();
    }

    //按籍贯查询
    void selectByPlace() throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("\n请输入学生籍贯：");
        String P = sc.next();

        Connection conn = null;
        conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement(selectsql);
        ps.setString(1, P);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println("学号：" + rs.getString("id") + "   姓名：" + rs.getString("name") + "   籍贯：" + rs.getString("place"));
        }
        conn.close();
    }


    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        conn = dataSource.getConnection();
        Statement sm = conn.createStatement();
        String creatsql = "CREATE TABLE if not exists `studentplace`.`student`  (`id` varchar(200) NOT NULL,`name` varchar(200) NOT NULL,`place` varchar(200) NOT NULL, PRIMARY KEY (`id`, `name`, `place`))";
        sm.executeUpdate(creatsql);
        conn.close();

        Student stu = new Student(null, null, null);
        while (true) {
            System.out.println("欢迎来到学生籍贯信息记录簿");
            System.out.println("请选择您想要进行的操作:");
            System.out.println("1.增加学生信息");
            System.out.println("2.按学号删除学生信息");
            System.out.println("3.按姓名删除学生信息");
            System.out.println("4.查询所有学生信息");
            System.out.println("5.按学号查询学生信息");
            System.out.println("6.按姓名查询学生信息");
            System.out.println("7.按籍贯查询学生信息");
            System.out.println("8.按学号修改学生籍贯");
            System.out.println("9.退出系统");
            System.out.print("->");
            Scanner sc = new Scanner(System.in);
            int choice = sc.nextInt();
            switch (choice) {
                case 1: {
                    stu.creatStudent();
                    break;
                }
                case 2: {
                    stu.deleteById();
                    break;
                }
                case 3: {
                    stu.deleteByName();
                    break;
                }
                case 4: {
                    stu.selectAll();
                    break;
                }
                case 5: {
                    stu.selectById();
                    break;
                }
                case 6: {
                    stu.selectByName();
                    break;
                }
                case 7: {
                    stu.selectByPlace();
                    break;
                }
                case 8: {
                    stu.setPlace();
                    break;
                }
                case 9: {
                    exit(0);
                }
                default: {
                    System.out.println("您的输入有误，请重新输入！");
                    break;
                }
            }
        }
    }
}
